# -*- coding: utf-8 -*-
import urllib2
import re
import random
import MySQLdb
from pydub import AudioSegment

def getAudio(word):
    try:
        a_url = "http://dict.youdao.com/dictvoice?audio="+word+"&type=2"
        data = urllib2.urlopen(a_url).read()
        with open("D:\Git\python-lite\sat\yd-"+word+".mp3", "wb") as f:
            f.write(data)
            f.close()
        audio = AudioSegment.from_mp3("D:\Git\python-lite\sat\yd-"+word+".mp3")
        audio.export("D:\Git\python-lite\sat\yd-"+word+".mp3", format='mp3',bitrate="16k")
    except:
        print word


words = open("D:\Git\python-lite\sat-1.txt")
p = re.compile(r".+\w+\.\s+(.+)$")

allist = []
temp = {}
i = 0
for line in words:
    if i ==0:
        temp["word"]=line.strip()
        #getAudio(temp["word"])
    if i ==1:
        m = p.match(line)
        if m:
            temp["zh"]=m.groups()[0]
        else:
            temp["zh"]=line.strip()
        allist.append(temp)
        temp={}
    i += 1
    if i==3:
        i=0
random.shuffle(allist)
print "changdu"
print len(allist)
db= MySQLdb.connect(
        host='60.205.94.133',
        port = 3306,
        user='root',
        passwd='totoro',
        db ='magic',
        charset='utf8',
        )
cursor = db.cursor()
class_id = 43
class_name = "SAT基础"
word_count = 14

round_id = 2499
orders = 1
i = 1
for w in allist:
    if i == 1:
        round_name = "第"+str(orders)+"关"
        round_id +=1
        round_desc = class_name + round_name
        sql =  '''INSERT INTO totoro_round(id, round_name, round_desc, class_id, class_name, word_count,orders)
                    VALUES ('%s', '%s', '%s', '%d', '%s', '%d', '%d')''' % (round_id, round_name, round_desc, class_id, class_name, word_count,orders)
        cursor.execute(sql)
        db.commit()

        orders+=1
    word = w["word"]
    word_zh = w["zh"]
    voice = 'yd-'+word
    sql = '''INSERT INTO word(round_id, round_name, class_id, class_name, word, word_zh, voice)
                VALUES('%d', '%s', '%d', '%s', '%s', '%s', '%s')''' % (round_id, round_name, class_id, class_name, word, word_zh, voice)
    cursor.execute(sql)
    db.commit()
    i +=1
    if i>14:
        i=1
db.close()
